package com.hdl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.hdl.dao.MallProductDao;
import com.hdl.entity.MallProduct;
import com.hdl.util.Druidutils;
import com.hdl.util.JdbcUtil;


public class MallProductDaolmpl implements MallProductDao{
	private Connection con = JdbcUtil.getConnection();
	
	private JdbcTemplate template = new JdbcTemplate(Druidutils.getDataSource());
	@Override
	public MallProduct SelectProductById(String id) {
		String sql="SELECT * FROM mall_product where MALL_PRODUCT_ID=?";
		PreparedStatement pstm = null;
		ResultSet eq = null;
		try {
			pstm = con.prepareStatement(sql);
			pstm.setObject(1, id);
			eq = pstm.executeQuery();
			MallProduct product = new MallProduct();
			while(eq.next()) {
				product.setMallProductId(eq.getString("MALL_PRODUCT_ID"));
				product.setMallTypeId(eq.getInt("MALL_TYPE_ID"));
				product.setMallProductName(eq.getString("MALL_PRODUCT_NAME"));
				product.setMallProductPrice1(eq.getInt("MALL_PRODUCT_PRICE_1"));
				product.setMallProductPrice2(eq.getInt("MALL_PRODUCT_PRICE_2"));
				product.setMallProductPath(eq.getString("MALL_PRODUCT_PATH"));
				product.setMallProductMs(eq.getString("MALL_PRODUCT_MS"));
				product.setMallProductStatus(eq.getInt("MALL_PRODUCT_STATUS"));
				product.setMallProductScore(eq.getInt("MALL_PRODUCT_SCORE"));
				return product;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	@Override
	public List<MallProduct> SelectAllProductByTypeId(int typeid) {
		String sql="SELECT * FROM mall_product where MALL_Type_ID=?";
		PreparedStatement pstm = null;
		ResultSet eq = null;
		List<MallProduct> list = new ArrayList<MallProduct>();
		try {
			pstm = con.prepareStatement(sql);
			pstm.setObject(1, typeid);
			System.out.println(sql);
			eq = pstm.executeQuery();
			MallProduct product=null;
			while(eq.next()) {
				product = new MallProduct();
				product.setMallProductId(eq.getString("MALL_PRODUCT_ID"));
				product.setMallTypeId(eq.getInt("MALL_TYPE_ID"));
				product.setMallProductName(eq.getString("MALL_PRODUCT_NAME"));
				product.setMallProductPrice1(eq.getInt("MALL_PRODUCT_PRICE_1"));
				product.setMallProductPrice2(eq.getInt("MALL_PRODUCT_PRICE_2"));
				product.setMallProductPath(eq.getString("MALL_PRODUCT_PATH"));
				product.setMallProductMs(eq.getString("MALL_PRODUCT_MS"));
				product.setMallProductStatus(eq.getInt("MALL_PRODUCT_STATUS"));
				product.setMallProductScore(eq.getInt("MALL_PRODUCT_SCORE"));
				list.add(product);
			}
			return list;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return null;
	}


	@Override
	public List<MallProduct> SelectProduct(int index, int pagemax, int typeid, String lk) {
	    List<MallProduct>  mpt = new ArrayList<MallProduct>();
	    StringBuffer sql = new StringBuffer("SELECT * FROM mall_product where 1=1");
	    if(!(typeid==0)) {
	    	sql.append(" AND MALL_TYPE_ID = "+typeid);
	    }
	    if(!("".equals(lk))&&!(null==lk)) {
	    	sql.append(" AND MALL_PRODUCT_NAME LIKE '%"+lk+"%'");
	    }
	    
	    sql.append(" LIMIT ?,?");
	    
	    PreparedStatement pstm = null;
		ResultSet eq = null;
		List<MallProduct> list = new ArrayList<MallProduct>();
		try {
			pstm = con.prepareStatement(sql.toString());
			pstm.setObject(1, index);
			pstm.setObject(2, pagemax);
			
			System.out.println(sql);
			eq = pstm.executeQuery();
			MallProduct product=null;
			while(eq.next()) {
				product = new MallProduct();
				product.setMallProductId(eq.getString("MALL_PRODUCT_ID"));
				product.setMallTypeId(eq.getInt("MALL_TYPE_ID"));
				product.setMallProductName(eq.getString("MALL_PRODUCT_NAME"));
				product.setMallProductPrice1(eq.getInt("MALL_PRODUCT_PRICE_1"));
				product.setMallProductPrice2(eq.getInt("MALL_PRODUCT_PRICE_2"));
				product.setMallProductPath(eq.getString("MALL_PRODUCT_PATH"));
				product.setMallProductMs(eq.getString("MALL_PRODUCT_MS"));
				product.setMallProductStatus(eq.getInt("MALL_PRODUCT_STATUS"));
				product.setMallProductScore(eq.getInt("MALL_PRODUCT_SCORE"));
				list.add(product);
			}
			return list;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}


	@Override
	public int Total(int typeid, String lk) {
		 StringBuffer sql = new StringBuffer("SELECT COUNT(*) FROM mall_product where 1=1");
		    if(!(typeid==0)) {
		    	sql.append(" AND MALL_TYPE_ID = "+typeid);
		    }
		    if(!("".equals(lk))&&!(null==lk)) {
		    	sql.append(" AND MALL_PRODUCT_NAME LIKE '%"+lk+"%'");
		    }
		    
		    PreparedStatement pstm=null;
		    ResultSet eq = null;
		    try {
				
		    	 pstm = con.prepareStatement(sql.toString());
		    	 eq = pstm.executeQuery();
		    	 while(eq.next()) {
		    		 
		    		 return eq.getInt("COUNT(*)");
		    	 }
			} catch (Exception e) {
			}
		    
		return 0;
	}


	@Override
	public void AddProduct(MallProduct mp) {
       String sql=" INSERT INTO mall_product VALUES (?,?,?,?,null,?,?,null,?)";
		
		template.update(sql, mp.getMallProductId(),mp.getMallTypeId(),mp.getMallProductName(),mp.getMallProductPrice1(),mp.getMallProductPath(),mp.getMallProductMs(),mp.getMallProductScore());
		
	}


	@Override
	public void DeleltProductById(String id) {
		

		String sql="delete from mall_product where MALL_PRODUCT_ID=?";
		template.update(sql,id);
	}


	@Override
	public void UpdateProduct(String id, MallProduct mp) {
		
		
		String sql=" UPDATE mall_product SET MALL_TYPE_ID = ?,MALL_PRODUCT_NAME = ? ,MALL_PRODUCT_PRICE_1 = ? ,MALL_PRODUCT_PATH = ? ,MALL_PRODUCT_SCORE = ?,MALL_PRODUCT_MS = ? WHERE MALL_PRODUCT_ID= ? ";
		
		template.update(sql, mp.getMallTypeId(),mp.getMallProductName(),mp.getMallProductPrice1(),mp.getMallProductPath(),mp.getMallProductScore(),mp.getMallProductMs(),mp.getMallProductId());
		
	}

}
